#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
创建存储过程和触发器
"""

import mysql.connector
from mysql.connector import Error

def create_procedures_and_triggers():
    """创建存储过程和触发器"""
    print("=== 创建存储过程和触发器 ===")
    
    # 获取数据库连接信息
    print("请输入MySQL连接信息:")
    host = input("主机地址 (默认: localhost): ").strip() or "localhost"
    port = input("端口 (默认: 3306): ").strip() or "3306"
    username = input("用户名 (默认: root): ").strip() or "root"
    password = input("密码: ").strip()
    
    try:
        port = int(port)
    except ValueError:
        print("错误: 端口必须是数字")
        return False
    
    try:
        connection = mysql.connector.connect(
            host=host,
            port=port,
            user=username,
            password=password,
            database='vxbattle'
        )
        
        cursor = connection.cursor()
        
        # 创建存储过程：计算挂机收益
        print("创建存储过程 CalculateIdleRewards...")
        
        # 先删除可能存在的存储过程
        try:
            cursor.execute("DROP PROCEDURE IF EXISTS CalculateIdleRewards")
        except:
            pass
        
        procedure_sql = """
CREATE PROCEDURE CalculateIdleRewards(
    IN p_character_id VARCHAR(36),
    OUT p_exp_gained BIGINT,
    OUT p_duration_seconds INT
)
BEGIN
    DECLARE v_start_time TIMESTAMP;
    DECLARE v_location_id INT;
    DECLARE v_base_exp_rate DECIMAL(10,2);
    DECLARE v_character_level INT;
    DECLARE v_base_exp_per_hour INT;
    DECLARE v_max_idle_hours INT;
    DECLARE v_actual_hours DECIMAL(10,2);
    
    -- 获取系统配置
    SELECT CAST(config_value AS UNSIGNED) INTO v_base_exp_per_hour 
    FROM system_config WHERE config_key = 'base_exp_per_hour';
    
    SELECT CAST(config_value AS UNSIGNED) INTO v_max_idle_hours 
    FROM system_config WHERE config_key = 'max_idle_hours';
    
    -- 获取角色当前挂机信息
    SELECT ir.start_time, ir.location_id, c.level
    INTO v_start_time, v_location_id, v_character_level
    FROM idle_records ir
    JOIN characters c ON ir.character_id = c.id
    WHERE ir.character_id = p_character_id AND ir.status = 'active'
    LIMIT 1;
    
    IF v_start_time IS NOT NULL THEN
        -- 计算挂机时长
        SET p_duration_seconds = TIMESTAMPDIFF(SECOND, v_start_time, NOW());
        
        -- 限制最大挂机时长
        IF p_duration_seconds > (v_max_idle_hours * 3600) THEN
            SET p_duration_seconds = v_max_idle_hours * 3600;
        END IF;
        
        SET v_actual_hours = p_duration_seconds / 3600.0;
        
        -- 获取地点经验倍率
        SELECT base_exp_rate INTO v_base_exp_rate
        FROM idle_locations WHERE id = v_location_id;
        
        -- 计算经验收益
        SET p_exp_gained = FLOOR(v_base_exp_per_hour * v_base_exp_rate * v_actual_hours * (1 + v_character_level * 0.1));
    ELSE
        SET p_exp_gained = 0;
        SET p_duration_seconds = 0;
    END IF;
END
        """
        
        cursor.execute(procedure_sql)
        print("✓ 存储过程 CalculateIdleRewards 创建成功")
        
        # 创建触发器：角色创建时自动开始挂机
        print("创建触发器 tr_character_auto_idle...")
        
        # 先删除可能存在的触发器
        try:
            cursor.execute("DROP TRIGGER IF EXISTS tr_character_auto_idle")
        except:
            pass
        
        trigger_sql = """
        CREATE TRIGGER tr_character_auto_idle
        AFTER INSERT ON characters
        FOR EACH ROW
        BEGIN
            -- 自动在新手村开始挂机
            INSERT INTO idle_records (character_id, location_id, start_time, status)
            VALUES (NEW.id, 1, NOW(), 'active');
        END
        """
        
        cursor.execute(trigger_sql)
        print("✓ 触发器 tr_character_auto_idle 创建成功")
        
        connection.commit()
        cursor.close()
        connection.close()
        
        print("\n✓ 所有存储过程和触发器创建完成")
        return True
        
    except Error as e:
        print(f"✗ 数据库错误: {e}")
        return False
    except Exception as e:
        print(f"✗ 异常: {e}")
        return False

def verify_procedures_and_triggers():
    """验证存储过程和触发器"""
    print("\n=== 验证存储过程和触发器 ===")
    
    # 获取数据库连接信息
    host = input("主机地址 (默认: localhost): ").strip() or "localhost"
    port = input("端口 (默认: 3306): ").strip() or "3306"
    username = input("用户名 (默认: root): ").strip() or "root"
    password = input("密码: ").strip()
    
    try:
        port = int(port)
    except ValueError:
        print("错误: 端口必须是数字")
        return False
    
    try:
        connection = mysql.connector.connect(
            host=host,
            port=port,
            user=username,
            password=password,
            database='vxbattle'
        )
        
        cursor = connection.cursor()
        
        # 检查存储过程
        cursor.execute("SHOW PROCEDURE STATUS WHERE Db = 'vxbattle'")
        procedures = [proc[1] for proc in cursor.fetchall()]
        print(f"数据库中的存储过程: {procedures}")
        
        if 'CalculateIdleRewards' in procedures:
            print("✓ 存储过程 CalculateIdleRewards 存在")
        else:
            print("✗ 存储过程 CalculateIdleRewards 不存在")
        
        # 检查触发器
        cursor.execute("SHOW TRIGGERS")
        triggers = [trigger[0] for trigger in cursor.fetchall()]
        print(f"数据库中的触发器: {triggers}")
        
        if 'tr_character_auto_idle' in triggers:
            print("✓ 触发器 tr_character_auto_idle 存在")
        else:
            print("✗ 触发器 tr_character_auto_idle 不存在")
        
        cursor.close()
        connection.close()
        
        success = 'CalculateIdleRewards' in procedures and 'tr_character_auto_idle' in triggers
        
        if success:
            print("\n✓ 所有存储过程和触发器验证通过")
        else:
            print("\n✗ 存储过程或触发器验证失败")
        
        return success
        
    except Error as e:
        print(f"✗ 数据库错误: {e}")
        return False
    except Exception as e:
        print(f"✗ 异常: {e}")
        return False

def main():
    """主函数"""
    print("VXBattle 存储过程和触发器创建工具")
    print("=" * 50)
    
    # 创建存储过程和触发器
    if create_procedures_and_triggers():
        # 验证创建结果
        if verify_procedures_and_triggers():
            print("\n🎉 存储过程和触发器创建成功！")
            print("\n现在可以运行游戏了:")
            print("  python src/main_mysql_gui.py")
        else:
            print("\n⚠️  验证失败，请检查创建结果")
    else:
        print("\n✗ 创建失败，请检查错误信息")

if __name__ == "__main__":
    main()